﻿using System;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;

namespace UDPSocketProject
{
    public class SqlHelper
    {
        public SqlHelper()
        {
        }

        #region  建立MySql数据库连接
        /// <summary>
        /// 建立数据库连接.
        /// </summary>
        /// <returns>返回MySqlConnection对象</returns>
        public MySqlConnection getmysqlcon()
        {
            //http://sosoft.cnblogs.com/
            string M_str_sqlcon = "server=localhost;user id=root;password=123456;database=HZRRoute"; //根据自己的设置
            MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
            return myCon;
        }
        #endregion

        #region  执行MySqlCommand命令
        /// <summary>
        /// 执行MySqlCommand
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        public void executMysqlcom(string M_str_sqlstr)
        {
            using (MySqlConnection mysqlcon = this.getmysqlcon())
            {
                mysqlcon.Open();
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
                mysqlcom.ExecuteNonQuery();
                //mysqlcom.Dispose();
                ////mysqlcon.Close();
                //mysqlcon.Dispose();
            }
        }
        #endregion

        #region  创建MySqlDataReader对象
        /// <summary>
        /// 创建一个MySqlDataReader对象
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <returns>返回MySqlDataReader对象</returns>
        public MySqlDataReader getmysqlread(string M_str_sqlstr)
        {
            using (MySqlConnection mysqlcon = this.getmysqlcon())
            {
                //MySqlConnection mysqlcon = this.getmysqlcon();
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
                mysqlcon.Open();
                MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                return mysqlread;
            }
        }

        public bool existsData(string existsSQL)
        {
            bool exists = false;
            using (MySqlConnection mysqlcon = this.getmysqlcon())
            {
                MySqlCommand mysqlcom = new MySqlCommand(existsSQL, mysqlcon);
                mysqlcon.Open();
                MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                exists = mysqlread.HasRows;
                //mysqlcon.Close();
                return exists;
            }
        }

        public string queryIdBySql(string querySql)
        {
            string idstr = "";
            using (MySqlConnection mysqlcon = this.getmysqlcon())
            {
                MySqlCommand mysqlcom = new MySqlCommand(querySql, mysqlcon);
                mysqlcon.Open();
                MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                while (mysqlread.Read())
                {
                    idstr = mysqlread.GetString(0);
                }
                //mysqlcon.Close();
                return idstr;
            }
        }

        #endregion

        #region 执行存储过程

        /// <summary>
        /// Execs the stored procedure.
        /// </summary>
        /// <returns>The stored procedure.</returns>
        /// <param name="procName">Proc name.</param>
        /// <param name="parameters">Parameters.</param>
        public int execStoredProcedure(string procName, params MySqlParameter[] parameters)
        {
            int execResult = 0;
            using (MySqlConnection conn = this.getmysqlcon())
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(procName, conn))
                {
                    MySqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        //cmd.CommandText = "CALL SavePhone(1,'3C-D9-2B-00-22-AA','3C-D9-2B-00-22-BB','Test009',-1,2);";
                        //cmd.CommandText = "SavePhone";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddRange(parameters);
                        execResult = cmd.ExecuteNonQuery();
                        st.Commit();
                        return execResult;
                    }
                    catch (SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }
                }
            }
        }

        public bool execStoredProcedure(string execProceSql)
        {
            bool execResult = false;
            using (MySqlConnection conn = this.getmysqlcon())
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(execProceSql, conn))
                {
                    MySqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandType = CommandType.Text;
                        execResult = cmd.ExecuteNonQuery() > -1;
                        st.Commit();
                        //conn.Close();
                        return execResult;
                    }
                    catch (SqlException sqlex)
                    {
                        st.Rollback();
                        throw sqlex;
                    }
                }
            }
        }

        #endregion
    }
}
